/*****************************************************/
/*** Product by Process Categorization ***************/
/*****************************************************/

/*EPO Claims*/

ALTER TABLE public.epo_01_claims_from_ep_xml
ADD COLUMN product_by_process_2 BOOLEAN ,
ADD COLUMN product_by_process_5 BOOLEAN
;


/*TODO: English */

UPDATE epo_01_claims_from_ep_xml

SET product_by_process_2 = CASE WHEN ((
                                      --Singular keywords
                                      (claim_text_ ~ '\maccording method\M|\maccording process\M|\maccording procedure\M|\mobtainable method\M|\mobtainable process\M|\mobtainable procedure\M|\mobtained method\M|\mobtained process\M|\mobtained procedure\M|\mproduced method\M|\mproduced process\M|\mproduced procedure\M|\mproducible method\M|\mproducible process\M|\mproducible procedure\M|\mpurified method\M|\mpurified process\M|\mpurified procedure\M|\maccordance method\M|\maccordance process\M|\maccordance procedure\M|\mmade method\M|\mmade process\M|\mmade procedure\M|\mmanufactured method\M|\mmanufactured process\M|\mmanufactured procedure\M|\mformed method\M|\mformed process\M|\mformed procedure\M|\mprepared method\M|\mprepared process\M|\mprepared procedure\M|\maccording application method\M|\maccording application process\M|\maccording application procedure\M|\maccordance application method\M|\maccordance application process\M|\maccordance application procedure\M')
                                      OR
                                      --Plural keywords
                                      (claim_text_ ~ '\maccording methods\M|\maccording processes\M|\maccording procedures\M|\mobtainable methods\M|\mobtainable processes\M|\mobtainable procedures\M|\mobtained methods\M|\mobtained processes\M|\mobtained procedures\M|\mproduced methods\M|\mproduced processes\M|\mproduced procedures\M|\mproducible methods\M|\mproducible processes\M|\mproducible procedures\M|\mpurified methods\M|\mpurified processes\M|\mpurified procedures\M|\maccordance methods\M|\maccordance processes\M|\maccordance procedures\M|\mmade methods\M|\mmade processes\M|\mmade procedures\M|\mmanufactured methods\M|\mmanufactured processes\M|\mmanufactured procedures\M|\mformed methods\M|\mformed processes\M|\mformed procedures\M|\mprepared methods\M|\mprepared processes\M|\mprepared procedures\M|\maccording application methods\M|\maccording application processes\M|\maccording application procedures\M|\maccordance application methods\M|\maccordance application processes\M|\maccordance application procedures\M')
                                    )
                                    AND
                                    (
                                      -- Exclude process keywords
                                      (array_to_string ((regexp_split_to_array(
                                        claim_text_, '\s+'))[1:2], ' ') !~ '\mprocess\M|\mmethod\M|\mprocedure\M|\mprocesses\M|\mmethods\M|\mprocedures\M')
                                      AND
                                      -- Exclude use keywords
                                      (split_part(claim_text_, ' ' ,1) !~ '\muse\M|\muses\M|\mutilization\M|\mutilizations\M|\mutilisation\M|\mutilisations\M|\musage\M|\musages\M')
                                    )
                                  ) THEN TRUE ELSE FALSE END ,


  product_by_process_5 = CASE WHEN ((
                                      --Singular keywords
                                      (claim_text_ ~ '\maccording method\M|\maccording process\M|\maccording procedure\M|\mobtainable method\M|\mobtainable process\M|\mobtainable procedure\M|\mobtained method\M|\mobtained process\M|\mobtained procedure\M|\mproduced method\M|\mproduced process\M|\mproduced procedure\M|\mproducible method\M|\mproducible process\M|\mproducible procedure\M|\mpurified method\M|\mpurified process\M|\mpurified procedure\M|\maccordance method\M|\maccordance process\M|\maccordance procedure\M|\mmade method\M|\mmade process\M|\mmade procedure\M|\mmanufactured method\M|\mmanufactured process\M|\mmanufactured procedure\M|\mformed method\M|\mformed process\M|\mformed procedure\M|\mprepared method\M|\mprepared process\M|\mprepared procedure\M|\maccording application method\M|\maccording application process\M|\maccording application procedure\M|\maccordance application method\M|\maccordance application process\M|\maccordance application procedure\M')
                                      OR
                                      --Plural keywords
                                      (claim_text_ ~ '\maccording methods\M|\maccording processes\M|\maccording procedures\M|\mobtainable methods\M|\mobtainable processes\M|\mobtainable procedures\M|\mobtained methods\M|\mobtained processes\M|\mobtained procedures\M|\mproduced methods\M|\mproduced processes\M|\mproduced procedures\M|\mproducible methods\M|\mproducible processes\M|\mproducible procedures\M|\mpurified methods\M|\mpurified processes\M|\mpurified procedures\M|\maccordance methods\M|\maccordance processes\M|\maccordance procedures\M|\mmade methods\M|\mmade processes\M|\mmade procedures\M|\mmanufactured methods\M|\mmanufactured processes\M|\mmanufactured procedures\M|\mformed methods\M|\mformed processes\M|\mformed procedures\M|\mprepared methods\M|\mprepared processes\M|\mprepared procedures\M|\maccording application methods\M|\maccording application processes\M|\maccording application procedures\M|\maccordance application methods\M|\maccordance application processes\M|\maccordance application procedures\M')
                                    )
                                    AND
                                    (
                                      -- Exclude process keywords
                                      (array_to_string ((regexp_split_to_array(
                                        claim_text_, '\s+'))[1:5], ' ') !~ '\mprocess\M|\mmethod\M|\mprocedure\M|\mprocesses\M|\mmethods\M|\mprocedures\M')
                                      AND
                                      -- Exclude use keywords
                                      (split_part(claim_text_, ' ' ,1) !~ '\muse\M|\muses\M|\mutilization\M|\mutilizations\M|\mutilisation\M|\mutilisations\M|\musage\M|\musages\M')
                                    )
                                  ) THEN TRUE ELSE FALSE END

WHERE claim_lang = 'en'
  AND claim_text IS NOT NULL
  AND claim_text != 'canceled'
  AND claim_text != ''
;



/* TODO: German */





/* TODO: French */






/***USPTO Claims ***/
ALTER TABLE public.epo_02_claims_from_us_csv
ADD COLUMN product_by_process_2 BOOLEAN ,
ADD COLUMN product_by_process_5 BOOLEAN
;



/*TODO: */
UPDATE epo_02_claims_from_us_csv

SET product_by_process_2 = CASE WHEN ((
                                      --Singular keywords
                                      (claim_text_ ~ '\maccording method\M|\maccording process\M|\maccording procedure\M|\mobtainable method\M|\mobtainable process\M|\mobtainable procedure\M|\mobtained method\M|\mobtained process\M|\mobtained procedure\M|\mproduced method\M|\mproduced process\M|\mproduced procedure\M|\mproducible method\M|\mproducible process\M|\mproducible procedure\M|\mpurified method\M|\mpurified process\M|\mpurified procedure\M|\maccordance method\M|\maccordance process\M|\maccordance procedure\M|\mmade method\M|\mmade process\M|\mmade procedure\M|\mmanufactured method\M|\mmanufactured process\M|\mmanufactured procedure\M|\mformed method\M|\mformed process\M|\mformed procedure\M|\mprepared method\M|\mprepared process\M|\mprepared procedure\M|\maccording application method\M|\maccording application process\M|\maccording application procedure\M|\maccordance application method\M|\maccordance application process\M|\maccordance application procedure\M')
                                      OR
                                      --Plural keywords
                                      (claim_text_ ~ '\maccording methods\M|\maccording processes\M|\maccording procedures\M|\mobtainable methods\M|\mobtainable processes\M|\mobtainable procedures\M|\mobtained methods\M|\mobtained processes\M|\mobtained procedures\M|\mproduced methods\M|\mproduced processes\M|\mproduced procedures\M|\mproducible methods\M|\mproducible processes\M|\mproducible procedures\M|\mpurified methods\M|\mpurified processes\M|\mpurified procedures\M|\maccordance methods\M|\maccordance processes\M|\maccordance procedures\M|\mmade methods\M|\mmade processes\M|\mmade procedures\M|\mmanufactured methods\M|\mmanufactured processes\M|\mmanufactured procedures\M|\mformed methods\M|\mformed processes\M|\mformed procedures\M|\mprepared methods\M|\mprepared processes\M|\mprepared procedures\M|\maccording application methods\M|\maccording application processes\M|\maccording application procedures\M|\maccordance application methods\M|\maccordance application processes\M|\maccordance application procedures\M')
                                    )
                                    AND
                                    (
                                      -- Exclude process keywords
                                      (array_to_string ((regexp_split_to_array(
                                        claim_text_, '\s+'))[1:2], ' ') !~ '\mprocess\M|\mmethod\M|\mprocedure\M|\mprocesses\M|\mmethods\M|\mprocedures\M')
                                      AND
                                      -- Exclude use keywords
                                      (split_part(claim_text_, ' ' ,1) !~ '\muse\M|\muses\M|\mutilization\M|\mutilizations\M|\mutilisation\M|\mutilisations\M|\musage\M|\musages\M')
                                    )
                                  ) THEN TRUE ELSE FALSE END ,


  product_by_process_5 = CASE WHEN ((
                                      --Singular keywords
                                      (claim_text_ ~ '\maccording method\M|\maccording process\M|\maccording procedure\M|\mobtainable method\M|\mobtainable process\M|\mobtainable procedure\M|\mobtained method\M|\mobtained process\M|\mobtained procedure\M|\mproduced method\M|\mproduced process\M|\mproduced procedure\M|\mproducible method\M|\mproducible process\M|\mproducible procedure\M|\mpurified method\M|\mpurified process\M|\mpurified procedure\M|\maccordance method\M|\maccordance process\M|\maccordance procedure\M|\mmade method\M|\mmade process\M|\mmade procedure\M|\mmanufactured method\M|\mmanufactured process\M|\mmanufactured procedure\M|\mformed method\M|\mformed process\M|\mformed procedure\M|\mprepared method\M|\mprepared process\M|\mprepared procedure\M|\maccording application method\M|\maccording application process\M|\maccording application procedure\M|\maccordance application method\M|\maccordance application process\M|\maccordance application procedure\M')
                                      OR
                                      --Plural keywords
                                      (claim_text_ ~ '\maccording methods\M|\maccording processes\M|\maccording procedures\M|\mobtainable methods\M|\mobtainable processes\M|\mobtainable procedures\M|\mobtained methods\M|\mobtained processes\M|\mobtained procedures\M|\mproduced methods\M|\mproduced processes\M|\mproduced procedures\M|\mproducible methods\M|\mproducible processes\M|\mproducible procedures\M|\mpurified methods\M|\mpurified processes\M|\maccordance methods\M|\maccordance processes\M|\maccordance procedures\M|\mmade methods\M|\mmade processes\M|\mmade procedures\M|\mmanufactured methods\M|\mmanufactured processes\M|\mmanufactured procedures\M|\mformed methods\M|\mformed processes\M|\mformed procedures\M|\mprepared methods\M|\mprepared processes\M|\mprepared procedures\M|\maccording application methods\M|\maccording application processes\M|\maccording application procedures\M|\maccordance application methods\M|\maccordance application processes\M|\maccordance application procedures\M')
                                    )
                                    AND
                                    (
                                      -- Exclude process keywords
                                      (array_to_string ((regexp_split_to_array(
                                        claim_text_, '\s+'))[1:5], ' ') !~ '\mprocess\M|\mmethod\M|\mprocedure\M|\mprocesses\M|\mmethods\M|\mprocedures\M')
                                      AND
                                      -- Exclude use keywords
                                      (split_part(claim_text_, ' ' ,1) !~ '\muse\M|\muses\M|\mutilization\M|\mutilizations\M|\mutilisation\M|\mutilisations\M|\musage\M|\musages\M')
                                    )
                                  ) THEN TRUE ELSE FALSE END

WHERE claim_txt IS NOT NULL
  AND claim_txt != 'canceled'
  AND claim_txt != ''
;





/*TODO: Set process classification to false where product_by_process is true*/

UPDATE epo_01_claims_from_ep_xml

  SET process_claims_2 = FALSE ,
      process_claims_5 = FALSE

WHERE product_by_process_2 = TRUE
   OR product_by_process_5 = TRUE
;


UPDATE epo_02_claims_from_us_csv

  SET process_claims_2 = FALSE ,
      process_claims_5 = FALSE

WHERE product_by_process_2 = TRUE
   OR product_by_process_5 = TRUE
;




/*****************************************************************/
/* Find German and French Keywords for Product by Process Claims */
/*****************************************************************/

CREATE TABLE epo_product_by_process_translations_de_fr AS

SELECT t1.*, t2.claim_lang, t2.claim_text AS claim_translation, t2.claim_text_ AS claim_translation_
FROM (
  SELECT publn_auth, pat_nr, publn_kind, claim_number_corrected, claim_text, claim_text_,
     CASE
        WHEN (claim_text_ ~ '\maccording method\M') THEN 'according method'
        WHEN (claim_text_ ~ '\maccording methods\M') THEN 'according methods'

        WHEN (claim_text_ ~ '\maccording process\M') THEN 'according process'
        WHEN (claim_text_ ~ '\maccording processes\M') THEN 'according processes'

        WHEN (claim_text_ ~ '\maccording procedure\M') THEN 'according procedure'
        WHEN (claim_text_ ~ '\maccording procedures\M') THEN 'according procedures'

        WHEN (claim_text_ ~ '\mobtainable method\M') THEN 'obtainable method'
        WHEN (claim_text_ ~ '\mobtainable methods\M') THEN 'obtainable methods'

        WHEN (claim_text_ ~ '\mobtainable process\M') THEN 'obtainable process'
        WHEN (claim_text_ ~ '\mobtainable processes\M') THEN 'obtainable processes'

        WHEN (claim_text_ ~ '\mobtainable procedure\M') THEN 'obtainable procedure'
        WHEN (claim_text_ ~ '\mobtainable procedures\M') THEN 'obtainable procedures'

        WHEN (claim_text_ ~ '\mobtained method\M') THEN 'obtained method'
        WHEN (claim_text_ ~ '\mobtained methods\M') THEN 'obtained methods'

        WHEN (claim_text_ ~ '\mobtained process\M') THEN 'obtained process'
        WHEN (claim_text_ ~ '\mobtained processes\M') THEN 'obtained processes'

        WHEN (claim_text_ ~ '\mobtained procedure\M') THEN 'obtained procedure'
        WHEN (claim_text_ ~ '\mobtained procedures\M') THEN 'obtained procedures'

        WHEN (claim_text_ ~ '\mproduced method\M') THEN 'produced method'
        WHEN (claim_text_ ~ '\mproduced methods\M') THEN 'produced methods'

        WHEN (claim_text_ ~ '\mproduced process\M') THEN 'produced process'
        WHEN (claim_text_ ~ '\mproduced processes\M') THEN 'produced processes'

        WHEN (claim_text_ ~ '\mproduced procedure\M') THEN 'produced procedure'
        WHEN (claim_text_ ~ '\mproduced procedures\M') THEN 'produced procedures'

        WHEN (claim_text_ ~ '\mproducible method\M') THEN 'producible method'
        WHEN (claim_text_ ~ '\mproducible methods\M') THEN 'producible methods'

        WHEN (claim_text_ ~ '\mproducible process\M') THEN 'producible process'
        WHEN (claim_text_ ~ '\mproducible processes\M') THEN 'producible processes'

        WHEN (claim_text_ ~ '\mproducible procedure\M') THEN 'producible procedure'
        WHEN (claim_text_ ~ '\mproducible procedures\M') THEN 'producible procedures'

        WHEN (claim_text_ ~ '\mpurified method\M') THEN 'purified method'
        WHEN (claim_text_ ~ '\mpurified methods\M') THEN 'purified methods'

        WHEN (claim_text_ ~ '\mpurified process\M') THEN 'purified process'
        WHEN (claim_text_ ~ '\mpurified processes\M') THEN 'purified processes'

        WHEN (claim_text_ ~ '\mpurified procedure\M') THEN 'purified procedure'
        WHEN (claim_text_ ~ '\mpurified procedures\M') THEN 'purified procedures'

        WHEN (claim_text_ ~ '\maccordance method\M') THEN 'accordance method'
        WHEN (claim_text_ ~ '\maccordance methods\M') THEN 'accordance methods'

        WHEN (claim_text_ ~ '\maccordance process\M') THEN 'accordance process'
        WHEN (claim_text_ ~ '\maccordance processes\M') THEN 'accordance processes'

        WHEN (claim_text_ ~ '\maccordance procedure\M') THEN 'accordance procedure'
        WHEN (claim_text_ ~ '\maccordance procedures\M') THEN 'accordance procedures'

        WHEN (claim_text_ ~ '\mmade method\M') THEN 'made method'
        WHEN (claim_text_ ~ '\mmade methods\M') THEN 'made methods'

        WHEN (claim_text_ ~ '\mmade process\M') THEN 'made process'
        WHEN (claim_text_ ~ '\mmade processes\M') THEN 'made processes'

        WHEN (claim_text_ ~ '\mmade procedure\M') THEN 'made procedure'
        WHEN (claim_text_ ~ '\mmade procedures\M') THEN 'made procedures'

        WHEN (claim_text_ ~ '\mmanufactured method\M') THEN 'manufactured method'
        WHEN (claim_text_ ~ '\mmanufactured methods\M') THEN 'manufactured methods'

        WHEN (claim_text_ ~ '\mmanufactured process\M') THEN 'manufactured process'
        WHEN (claim_text_ ~ '\mmanufactured processes\M') THEN 'manufactured processes'

        WHEN (claim_text_ ~ '\mmanufactured procedure\M') THEN 'manufactured procedure'
        WHEN (claim_text_ ~ '\mmanufactured procedures\M') THEN 'manufactured procedures'

        WHEN (claim_text_ ~ '\mformed method\M') THEN 'formed method'
        WHEN (claim_text_ ~ '\mformed methods\M') THEN 'formed methods'

        WHEN (claim_text_ ~ '\mformed process\M') THEN 'formed process'
        WHEN (claim_text_ ~ '\mformed processes\M') THEN 'formed processes'

        WHEN (claim_text_ ~ '\mformed procedure\M') THEN 'formed procedure'
        WHEN (claim_text_ ~ '\mformed procedures\M') THEN 'formed procedures'

        WHEN (claim_text_ ~ '\mprepared method\M') THEN 'prepared method'
        WHEN (claim_text_ ~ '\mprepared methods\M') THEN 'prepared methods'

        WHEN (claim_text_ ~ '\mprepared process\M') THEN 'prepared process'
        WHEN (claim_text_ ~ '\mprepared processes\M') THEN 'prepared processes'

        WHEN (claim_text_ ~ '\mprepared procedure\M') THEN 'prepared procedure'
        WHEN (claim_text_ ~ '\mprepared procedures\M') THEN 'prepared procedures'

        WHEN (claim_text_ ~ '\maccording application method\M') THEN 'according application method'
        WHEN (claim_text_ ~ '\maccording application methods\M') THEN 'according application methods'

        WHEN (claim_text_ ~ '\maccording application process\M') THEN 'according application process'
        WHEN (claim_text_ ~ '\maccording application processes\M') THEN 'according application processes'

        WHEN (claim_text_ ~ '\maccording application procedure\M') THEN 'according application procedure'
        WHEN (claim_text_ ~ '\maccording application procedures\M') THEN 'according application procedures'

        WHEN (claim_text_ ~ '\maccordance application method\M') THEN 'accordance application method'
        WHEN (claim_text_ ~ '\maccordance application methods\M') THEN 'accordance application methods'

        WHEN (claim_text_ ~ '\maccordance application process\M') THEN 'accordance application process'
        WHEN (claim_text_ ~ '\maccordance application processes\M') THEN 'accordance application processes'

        WHEN (claim_text_ ~ '\maccordance application procedure\M') THEN 'accordance application procedure'
        WHEN (claim_text_ ~ '\maccordance application procedures\M') THEN 'accordance application procedures'

      END AS classification_keywords

  FROM epo_01_claims_from_ep_xml
    WHERE product_by_process_1 IS TRUE
       OR product_by_process_2 IS TRUE


) t1
JOIN epo_01_claims_from_ep_xml t2 ON t1.publn_auth             = t2.publn_auth
                                 AND t1.pat_nr                 = t2.pat_nr
                                 AND t1.publn_kind             = t2.publn_kind
                                 AND t1.claim_number_corrected = t2.claim_number_corrected

WHERE t2.claim_lang IN ('de', 'fr')
;







SELECT *
  FROM epo_product_by_process_translations_de_fr
    WHERE claim_lang = 'de'
;


SELECT count(distinct classification_keywords)
FROM epo_product_by_process_translations_de_fr
WHERE claim_lang = 'fr'
;

SELECT distinct classification_keywords
FROM epo_product_by_process_translations_de_fr
;



SELECT
  *
FROM (
  SELECT
    ROW_NUMBER() OVER (PARTITION BY classification_keywords) AS r,
    t.*
  FROM
    epo_product_by_process_translations_de_fr t

  WHERE claim_lang = 'de'

     order by random()) x
WHERE
  x.r <= 5

  ORDER BY classification_keywords asc, claim_lang

;











/*****************************************************/
/*** Older Versions **********************************/
/*****************************************************/

ALTER TABLE epo_01_claims_from_ep_xml
ADD product_by_process boolean;
;

UPDATE epo_01_claims_from_ep_xml

SET product_by_process = CASE WHEN (
          (
             claim_text_ like '% according method %'
          OR claim_text_ like '% according process %'
          OR claim_text_ like '% according procedure %'
                 
                    
          OR claim_text_ like '% obtainable method %'
          OR claim_text_ like '% obtainable process %'
          OR claim_text_ like '% obtainable procedure %'
          
          OR claim_text_ like '% obtained method %'
          OR claim_text_ like '% obtained process %'
          OR claim_text_ like '% obtained procedure %'
          
          OR claim_text_ like '% produced method %'
          OR claim_text_ like '% produced process %' 
          OR claim_text_ like '% produced procedure %'
          
          OR claim_text_ like '% producible method %'
          OR claim_text_ like '% producible process %' 
          OR claim_text_ like '% producible procedure %'
          
          OR claim_text_ like '% purified method %'
          OR claim_text_ like '% purified process %'
          OR claim_text_ like '% purified procedure %'
                    
          OR claim_text_ like '% accordance method %'
          OR claim_text_ like '% accordance process %'
          OR claim_text_ like '% accordance procedure %'
                    
          OR claim_text_ like '% made method %'
          OR claim_text_ like '% made process %'
          OR claim_text_ like '% made procedure %'
                    
          OR claim_text_ like '% manufactured method %'
          OR claim_text_ like '% manufactured process %'
          OR claim_text_ like '% manufactured procedure %'
                    
          OR claim_text_ like '% method comprises steps %' 
          OR claim_text_ like '% process comprises steps %'
          OR claim_text_ like '% procedure comprises steps %'
         
          OR claim_text_ like '% formed method %'
          OR claim_text_ like '% formed process %'
          OR claim_text_ like '% formed procedure %'
          
          OR claim_text_ like '% prepared method %'
          OR claim_text_ like '% prepared process %'
          OR claim_text_ like '% prepared procedure %'

        -- Duplicates
        -- OR claim_text_ like '% according method %'
        -- OR claim_text_ like '% according process %'
        -- OR claim_text_ like '% according procedure %'
                    
          OR claim_text_ like '% according application method %'
          OR claim_text_ like '% according application process %'
          OR claim_text_ like '% according application procedure %'
          
          -- add: prepared !
          
          
          
          )
          -- process keywords are not allowed to come at the beginning! 
          and claim_text_1 !~ '\mprocess\M'
          and claim_text_1 !~ '\mmethod\M'
          and claim_text_1 !~ '\mprocedure\M'
          
          --what if a phrase such as "information processing method" comes at the beginning? 
          --with claim_text_1 !~ '\mmethod\M' it is not filtered out
          --with claim_text_concat !~'\mmethod\M' (including the first 4 words) we would filter out positive cases, e.g. "composition prepared method"
          
          
          ---please add all other process keywords here!! 
		  
          ) THEN TRUE

     ELSE FALSE
     END

WHERE claim_lang = 'en'
;


--Alternative:
UPDATE epo_01_claims_from_ep_xml

SET product_by_process =  claim_text_ ~ '\maccording method\M|
                                        \maccording process\M|
                                        \maccording procedure\M|
                                        \mobtainable method\M|
                                        \mobtainable process\M|
                                        \mobtainable procedure\M|
                                        \mobtained method\M|
                                        \mobtained process\M|
                                        \mobtained procedure\M|
                                        \mproduced method\M|
                                        \mproduced process\M|
                                        \mproduced procedure\M|
                                        \mproducible method\M|
                                        \mproducible process\M|
                                        \mproducible procedure\M|
                                        \mpurified method\M|
                                        \mpurified process\M|
                                        \mpurified procedure\M|
                                        \maccordance method\M|
                                        \maccordance process\M|
                                        \maccordance procedure\M|
                                        \mmade method\M|
                                        \mmade process\M|
                                        \mmade procedure\M|
                                        \mmanufactured method\M|
                                        \mmanufactured process\M|
                                        \mmanufactured procedure\M|
                                        \mmethod comprises steps\M|
                                        \mprocess comprises steps\M|
                                        \mprocedure comprises steps\M|
                                        \mformed method\M|
                                        \mformed process\M|
                                        \mformed procedure\M|
                                        \mprepared method\M|
                                        \mprepared process\M|
                                        \mprepared procedure\M|
                                        \maccording application method\M|
                                        \maccording application process\M|
                                        \maccording application procedure\M'


  WHERE split_part(claim_text_, ' ' ,1) !~ '\mprocess\M|\mmethod\M|\mprocedure\M'

  AND claim_lang = 'en'
;



--Alternative FULL Process:
UPDATE epo_01_claims_from_ep_xml

SET product_by_process = TRIM(LEADING FROM
                          regexp_replace(
                          regexp_replace(
                          regexp_replace(
                            regexp_replace(
                                regexp_replace(
                                    translate(lower(claim_text),
                                  'àâãäåāăąÁÂÃÄÅĀĂĄèééêëēĕėęěĒĔĖĘĚìíîïìĩīĭÌÍÎÏÌĨĪĬóôõöōŏőÒÓÔÕÖŌŎŐùúûüũūŭůÙÚÛÜŨŪŬŮ',
                                  'aaaaaaaaaaaaaaaaeeeeeeeeeeeeeeeiiiiiiiiiiiiiiiiooooooooooooooouuuuuuuuuuuuuuuu'), '\d*\s*\W\d*\W*\s+', ' ', 'g'),
                            '\y(a|about|above|after|again|against|all|am|an|and|any|are|as|at|be|because|been|before|being|below|between|both|but|by|could|did|do|does|doing|down|during|each|few|for|from|further|had|has|have|having|he|her|here|hers|herself|him|himself|his|how|i|if|in|into|is|it|its|itself|me|more|most|my|myself|nor|of|on|once|only|or|other|ought|our|ours|ourselves|out|over|own|same|she|should|so|some|such|than|that|the|their|theirs|them|themselves|then|there|these|they|this|those|through|to|too|under|until|up|very|was|we|were|what|when|where|which|while|who|whom|why|with|would|you|your|yours|yourself|yourselves|i|me|my|myself|we|our|ours|ourselves|you|your|yours|yourself|yourselves|he|him|his|himself|she|her|hers|herself|it|its|itself|they|them|their|theirs|themselves|what|which|who|whom|this|that|these|those|am|is|are|was|were|be|been|being|have|has|had|having|do|does|did|doing|a|an|the|and|but|if|or|because|as|until|while|of|at|by|for|with|about|against|between|into|through|during|before|after|above|below|to|from|up|down|in|out|on|off|over|under|again|further|then|once|here|there|when|where|why|how|all|any|both|each|few|more|most|other|some|such|no|nor|not|only|own|same|so|than|too|very|s|t|can|will|just|don|should|now|au|aux|avec|ce|ces|dans|de|des|du|elle|en|et|eux|il|je|la|le|leur|lui|ma|mais|me|même|mes|moi|mon|ne|nos|notre|nous|on|ou|par|pas|pour|qu|que|qui|sa|se|ses|son|sur|ta|te|tes|toi|ton|tu|un|une|vos|votre|vous|c|d|j|l|à|m|n|s|t|y|été|étée|étées|étés|étant|étante|étants|étantes|suis|es|est|sommes|êtes|sont|serai|seras|sera|serons|serez|seront|serais|serait|serions|seriez|seraient|étais|était|étions|étiez|étaient|fus|fut|fûmes|fûtes|furent|sois|soit|soyons|soyez|soient|fusse|fusses|fût|fussions|fussiez|fussent|ayant|ayante|ayantes|ayants|eu|eue|eues|eus|ai|as|avons|avez|ont|aurai|auras|aura|aurons|aurez|auront|aurais|aurait|aurions|auriez|auraient|avais|avait|avions|aviez|avaient|eut|eûmes|eûtes|eurent|aie|aies|ait|ayons|ayez|aient|eusse|eusses|eût|eussions|eussiez|eussent|aber|alle|allem|allen|aller|alles|als|also|am|an|ander|andere|anderem|anderen|anderer|anderes|anderm|andern|anderr|anders|auch|auf|aus|bei|bin|bis|bist|da|damit|dann|der|den|des|dem|die|das|daß|derselbe|derselben|denselben|desselben|demselben|dieselbe|dieselben|dasselbe|dazu|dein|deine|deinem|deinen|deiner|deines|denn|derer|dessen|dich|dir|du|dies|diese|diesem|diesen|dieser|dieses|doch|dort|durch|ein|eine|einem|einen|einer|eines|einig|einige|einigem|einigen|einiger|einiges|einmal|er|ihn|ihm|es|etwas|euer|eure|eurem|euren|eurer|eures|für|gegen|gewesen|hab|habe|haben|hat|hatte|hatten|hier|hin|hinter|ich|mich|mir|ihr|ihre|ihrem|ihren|ihrer|ihres|euch|im|in|indem|ins|ist|jede|jedem|jeden|jeder|jedes|jene|jenem|jenen|jener|jenes|jetzt|kann|kein|keine|keinem|keinen|keiner|keines|können|könnte|machen|man|manche|manchem|manchen|mancher|manches|mein|meine|meinem|meinen|meiner|meines|mit|muss|musste|nach|nicht|nichts|noch|nun|nur|ob|oder|ohne|sehr|sein|seine|seinem|seinen|seiner|seines|selbst|sich|sie|ihnen|sind|so|solche|solchem|solchen|solcher|solches|soll|sollte|sondern|sonst|über|um|und|uns|unse|unsem|unsen|unser|unses|unter|viel|vom|von|vor|während|war|waren|warst|was|weg|weil|weiter|welche|welchem|welchen|welcher|welches|wenn|werde|werden|wie|wieder|will|wir|wird|wirst|wo|wollen|wollte|würde|würden|zu|zum|zur|zwar|zwischen|wherein|thereof|dadurch|dass|wobei|gekennzeichnet)\y', '', 'g'), '\s+', ' ', 'g'
                            ), '\W+', ' ', 'g')
                            , '\m[a-z]{1}\M', '
', 'g')) ~ '\maccording method\M|
                                                           \maccording process\M|
                                                           \maccording procedure\M|
                                                           \mobtainable method\M|
                                                           \mobtainable process\M|
                                                           \mobtainable procedure\M|
                                                           \mobtained method\M|
                                                           \mobtained process\M|
                                                           \mobtained procedure\M|
                                                           \mproduced method\M|
                                                           \mproduced process\M|
                                                           \mproduced procedure\M|
                                                           \mproducible method\M|
                                                           \mproducible process\M|
                                                           \mproducible procedure\M|
                                                           \mpurified method\M|
                                                           \mpurified process\M|
                                                           \mpurified procedure\M|
                                                           \maccordance method\M|
                                                           \maccordance process\M|
                                                           \maccordance procedure\M|
                                                           \mmade method\M|
                                                           \mmade process\M|
                                                           \mmade procedure\M|
                                                           \mmanufactured method\M|
                                                           \mmanufactured process\M|
                                                           \mmanufactured procedure\M|
                                                           \mmethod comprises steps\M|
                                                           \mprocess comprises steps\M|
                                                           \mprocedure comprises steps\M|
                                                           \mformed method\M|
                                                           \mformed process\M|
                                                           \mformed procedure\M|
                                                           \mprepared method\M|
                                                           \mprepared process\M|
                                                           \mprepared procedure\M|
                                                           \maccording application method\M|
                                                           \maccording application process\M|
                                                           \maccording application procedure\M'

  WHERE split_part(TRIM(LEADING FROM
                      regexp_replace(
                      regexp_replace(
                      regexp_replace(
                        regexp_replace(
                            regexp_replace(
                                translate(lower(claim_text),
                              'àâãäåāăąÁÂÃÄÅĀĂĄèééêëēĕėęěĒĔĖĘĚìíîïìĩīĭÌÍÎÏÌĨĪĬóôõöōŏőÒÓÔÕÖŌŎŐùúûüũūŭůÙÚÛÜŨŪŬŮ',
                              'aaaaaaaaaaaaaaaaeeeeeeeeeeeeeeeiiiiiiiiiiiiiiiiooooooooooooooouuuuuuuuuuuuuuuu'), '\d*\s*\W\d*\W*\s+', ' ', 'g'),
                        '\y(a|about|above|after|again|against|all|am|an|and|any|are|as|at|be|because|been|before|being|below|between|both|but|by|could|did|do|does|doing|down|during|each|few|for|from|further|had|has|have|having|he|her|here|hers|herself|him|himself|his|how|i|if|in|into|is|it|its|itself|me|more|most|my|myself|nor|of|on|once|only|or|other|ought|our|ours|ourselves|out|over|own|same|she|should|so|some|such|than|that|the|their|theirs|them|themselves|then|there|these|they|this|those|through|to|too|under|until|up|very|was|we|were|what|when|where|which|while|who|whom|why|with|would|you|your|yours|yourself|yourselves|i|me|my|myself|we|our|ours|ourselves|you|your|yours|yourself|yourselves|he|him|his|himself|she|her|hers|herself|it|its|itself|they|them|their|theirs|themselves|what|which|who|whom|this|that|these|those|am|is|are|was|were|be|been|being|have|has|had|having|do|does|did|doing|a|an|the|and|but|if|or|because|as|until|while|of|at|by|for|with|about|against|between|into|through|during|before|after|above|below|to|from|up|down|in|out|on|off|over|under|again|further|then|once|here|there|when|where|why|how|all|any|both|each|few|more|most|other|some|such|no|nor|not|only|own|same|so|than|too|very|s|t|can|will|just|don|should|now|au|aux|avec|ce|ces|dans|de|des|du|elle|en|et|eux|il|je|la|le|leur|lui|ma|mais|me|même|mes|moi|mon|ne|nos|notre|nous|on|ou|par|pas|pour|qu|que|qui|sa|se|ses|son|sur|ta|te|tes|toi|ton|tu|un|une|vos|votre|vous|c|d|j|l|à|m|n|s|t|y|été|étée|étées|étés|étant|étante|étants|étantes|suis|es|est|sommes|êtes|sont|serai|seras|sera|serons|serez|seront|serais|serait|serions|seriez|seraient|étais|était|étions|étiez|étaient|fus|fut|fûmes|fûtes|furent|sois|soit|soyons|soyez|soient|fusse|fusses|fût|fussions|fussiez|fussent|ayant|ayante|ayantes|ayants|eu|eue|eues|eus|ai|as|avons|avez|ont|aurai|auras|aura|aurons|aurez|auront|aurais|aurait|aurions|auriez|auraient|avais|avait|avions|aviez|avaient|eut|eûmes|eûtes|eurent|aie|aies|ait|ayons|ayez|aient|eusse|eusses|eût|eussions|eussiez|eussent|aber|alle|allem|allen|aller|alles|als|also|am|an|ander|andere|anderem|anderen|anderer|anderes|anderm|andern|anderr|anders|auch|auf|aus|bei|bin|bis|bist|da|damit|dann|der|den|des|dem|die|das|daß|derselbe|derselben|denselben|desselben|demselben|dieselbe|dieselben|dasselbe|dazu|dein|deine|deinem|deinen|deiner|deines|denn|derer|dessen|dich|dir|du|dies|diese|diesem|diesen|dieser|dieses|doch|dort|durch|ein|eine|einem|einen|einer|eines|einig|einige|einigem|einigen|einiger|einiges|einmal|er|ihn|ihm|es|etwas|euer|eure|eurem|euren|eurer|eures|für|gegen|gewesen|hab|habe|haben|hat|hatte|hatten|hier|hin|hinter|ich|mich|mir|ihr|ihre|ihrem|ihren|ihrer|ihres|euch|im|in|indem|ins|ist|jede|jedem|jeden|jeder|jedes|jene|jenem|jenen|jener|jenes|jetzt|kann|kein|keine|keinem|keinen|keiner|keines|können|könnte|machen|man|manche|manchem|manchen|mancher|manches|mein|meine|meinem|meinen|meiner|meines|mit|muss|musste|nach|nicht|nichts|noch|nun|nur|ob|oder|ohne|sehr|sein|seine|seinem|seinen|seiner|seines|selbst|sich|sie|ihnen|sind|so|solche|solchem|solchen|solcher|solches|soll|sollte|sondern|sonst|über|um|und|uns|unse|unsem|unsen|unser|unses|unter|viel|vom|von|vor|während|war|waren|warst|was|weg|weil|weiter|welche|welchem|welchen|welcher|welches|wenn|werde|werden|wie|wieder|will|wir|wird|wirst|wo|wollen|wollte|würde|würden|zu|zum|zur|zwar|zwischen|wherein|thereof|dadurch|dass|wobei|gekennzeichnet)\y', '', 'g'), '\s+', ' ', 'g'
                        ), '\W+', ' ', 'g')
                        , '\m[a-z]{1}\M', '', 'g')) , ' ' ,1) !~ '\mprocess\M|\mmethod\M|\mprocedure\M'

  AND claim_lang = 'en'
;


